package com.service.database.action;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.common.utils.constant.SQLConstants;
import com.service.database.model.DataColumn;
import com.service.database.model.DataModel;
import com.service.database.config.DataBase;
import com.service.database.config.DatabaseConfig;
import com.service.database.execute.DatabaseEngine;
import com.service.database.execute.Query;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * jdbc执行器
 * @author xianggj
 * @Date 2021/6/4 18:42
 **/
public class JDBCExecutor {


    /**
     * 查询数据库列表
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static List<Object> listDatabases(DatabaseConfig databaseConfig) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType());
        Query query = new Query(databaseConfig, sqlAdapter.getShowDataBaseSql()) {
            @Override
            public Object doProcess() throws Exception {
                List<Map<String, Object>> rs = getRs();
                String key = sqlAdapter.getReturnKeys().get(0);
                List<Object> list = rs.stream()
                        .map(data -> data.get(key)).collect(Collectors.toList());
                return list;
            }
        };
        return (List<Object>)new DatabaseEngine().executeQuery(query);
    }

    /**
     * 查询数据表列表
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> listTables(DatabaseConfig databaseConfig) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType());
        sqlAdapter.setSchema(databaseConfig.getSchema());
        Query query = new Query(databaseConfig, sqlAdapter.getShowTablesSql()) {
            @Override
            public Object doProcess() throws Exception {
                List<Map<String, Object>> rs = getRs();
                return rs;
            }
        };
        query.addParamers(databaseConfig.getDbName());
        return (List<Map<String, Object>>)new DatabaseEngine().executeQuery(query);
    }

    /**
     * 创建表
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static void createTable(DatabaseConfig databaseConfig, List<DataColumn> columns) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName());
        List<String> tableSql = sqlAdapter.createTableSql(columns);
        Query query = new Query(databaseConfig, tableSql.get(0)) {
            @Override
            public Object doProcess() throws Exception {
                List<Map<String, Object>> rs = getRs();
                return rs;
            }
        };
        tableSql.remove(0);
        query.setBatchSql(tableSql);
        new DatabaseEngine().executeBatch(query);
    }


    /**
     * 删除表
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static void dropTable(DatabaseConfig databaseConfig) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName());
        Query query = new Query(databaseConfig, sqlAdapter.dropTableSql()) {
            @Override
            public Object doProcess() throws Exception {
                List<Map<String, Object>> rs = getRs();
                return rs;
            }
        };
        new DatabaseEngine().execute(query);
    }


    /**
     * 查询数据表详情
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static List<Map<String,Object>> listTableDesc(DatabaseConfig databaseConfig) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName());
        Query query = new Query(databaseConfig, sqlAdapter.getShowTableDescSql()) {
            @Override
            public Object doProcess() throws Exception {
                return getRs();
            }
        };
        query.addParamers(databaseConfig.getTableName());
        return (List<Map<String,Object>>)new DatabaseEngine().executeQuery(query);
    }

    /**
     * 查询多列
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static List<Map<String,Object>> searchCols(DatabaseConfig databaseConfig, String... cols) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName())
                .setCols(cols);
        Query query = new Query(databaseConfig, sqlAdapter.distinctColsSql()) {
            @Override
            public Object doProcess() throws Exception {
                return getRs();
            }
        };
        return (List<Map<String,Object>>)new DatabaseEngine().executeQuery(query);
    }

    /**
     * 查询单列
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static List<Object> distinctCol(DatabaseConfig databaseConfig, String col) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName())
                .setCols(col);
        //别名 避免关键字别名
        final String alias = "value";
        Query query = new Query(databaseConfig, sqlAdapter.distinctColSql(alias)) {
            @Override
            public Object doProcess() throws Exception {
                return getRs();
            }
        };
        List<Map<String, Object>> maps = (List<Map<String, Object>>) new DatabaseEngine().executeQuery(query);
        return maps.stream().map(e->e.get(alias)).collect(Collectors.toList());
    }

    /**
     * 查询数据表数据
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static DataModel listTableDatas(DatabaseConfig databaseConfig) throws Exception{
        DataModel dataModel = listTableDatas(databaseConfig, null, null);
        return dataModel;
    }
    /**
     * 插入数据
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static void insertDatas(DatabaseConfig databaseConfig, String[] cols, List<Map<String,Object>>  datas) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName())
                .setCols(cols);
        Query query = new Query(databaseConfig, sqlAdapter.createInsertSQL()) {
            @Override
            public Object doProcess() throws Exception {
                return getRs();
            }
        };
        List<Object[]> objs = getValues(cols, datas);
        new DatabaseEngine().executeBatch(query, objs);
    }
    /**
     * 更新数据
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static void updateDatas(DatabaseConfig databaseConfig, String[] cols,
                                   List<Map<String,Object>>  datas, String columnKey) throws Exception{
        if (!ArrayUtil.contains(cols, columnKey)){
            throw new RuntimeException("执行更新操作的数据必须存在主键列:"+ columnKey);
        } else {
            //将id列移动到最后一列
            cols = ArrayUtil.removeEle(cols, columnKey);
            cols = ArrayUtil.insert(cols, cols.length, columnKey);
        }
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName())
                .setCols(cols)
                .setColumnKey(columnKey);
        Query query = new Query(databaseConfig, sqlAdapter.createUpdateSQL()) {
            @Override
            public Object doProcess() throws Exception {
                return getRs();
            }
        };
        List<Object[]> objs = new ArrayList<>();
        for (Map<String, Object> data : datas) {
            Object[] obj = new Object[cols.length];
            for (int i = 0; i < cols.length; i++) {
                obj[i] = data.get(cols[i]);
            }
            objs.add(obj);
        }
        new DatabaseEngine().executeBatch(query, objs);
    }

    private static List<Object[]> getValues(String[] cols, List<Map<String, Object>> datas) {
        List<Object[]> objs = new ArrayList<>();
        for (Map<String, Object> data : datas) {
            Object[] obj = new Object[cols.length];
            for (int i = 0; i < cols.length; i++) {
                obj[i] = data.get(cols[i]);
            }
            objs.add(obj);
        }
        return objs;
    }

    /**
     * 替换数据
     * @param databaseConfig
     * @return
     * @throws Exception
     */
    public static void replaceDatas(DatabaseConfig databaseConfig, String[] cols, List<Map<String,Object>>  datas) throws Exception{
        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName())
                .setCols(cols);
        Query query = new Query(databaseConfig, sqlAdapter.createReplaceSQL()) {
            @Override
            public Object doProcess() throws Exception {
                return getRs();
            }
        };
        List<Object[]> objs = getValues(cols, datas);
        new DatabaseEngine().executeBatch(query, objs);
    }

    /**
     * 查询数据表数据
     * @param databaseConfig
     * @param num 开始位置
     * @param offset 跨度
     * @return
     * @throws Exception
     */
    public static DataModel listTableDatas(DatabaseConfig databaseConfig, Integer num, Integer offset) throws Exception{

        SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                .setDbName(databaseConfig.getDbName())
                .setSchema(databaseConfig.getSchema())
                .setTableName(databaseConfig.getTableName());
        Query query = new Query(databaseConfig, sqlAdapter.getShowTableDataSql(), num, offset) {
            @Override
            public Object doProcess() throws Exception {
                DataModel dataModel = new DataModel();
                List<Map<String, Object>> rs = getRs();
                List<DataColumn>  cols = getCols();
                
                //处理date类型 取消显示时间
                List<String> dateColumns = cols.stream().filter(dc -> StrUtil.equalsIgnoreCase(dc.getDataType(), "date")).map(dc -> dc.getColumnName()).collect(Collectors.toList());
                if(CollectionUtil.isNotEmpty(dateColumns)) {
                    rs.stream().forEach(data -> {
                        for (String col : dateColumns) {
                            if(ObjectUtil.isNotNull(data.get(col))) {
                                data.put(col, DateUtil.formatDate((Date) data.get(col)));
                            }
                        }
                    });
                }

                //特殊处理bool类型 显示为t 或者 f
                List<String> boolColumns = cols.stream().filter(dc -> StrUtil.equalsIgnoreCase(dc.getDataType(), "bool")).map(dc -> dc.getColumnName()).collect(Collectors.toList());
                if(CollectionUtil.isNotEmpty(boolColumns)) {
                    rs.stream().forEach(data -> {
                        for (String col : boolColumns) {
                            if(ObjectUtil.isNotNull(data.get(col))) {
                                data.put(col, Convert.toBool(data.get(col)) ? "t" : "f");
                            }
                        }
                    });
                }

                dataModel.setDatas(rs);
                dataModel.setColumns(cols);
                return dataModel;
            }
        };
        DataModel dataModel = (DataModel) new DatabaseEngine().executeQuerySort(query);
        Query queryNum = new Query(databaseConfig, sqlAdapter.getShowTableDataNumSql()) {
            @Override
            public Object doProcess() throws Exception {
                String key = sqlAdapter.getReturnKeys().get(0);
                List<Map<String, Object>> rs = getRs();
                Object num = rs.get(0).get(key);
                return Long.valueOf(num.toString());
            }
        };
        Long aLong = (Long) new DatabaseEngine().executeQuery(queryNum);
        dataModel.setCount(aLong);
        return dataModel;
    }

    /**
     * 测试数据库连接
     * @param databaseConfig
     * @return
     */
    public static boolean testConnect(DatabaseConfig databaseConfig){
        try {
            Class.forName(databaseConfig.getDriver());
            DriverManager.setLoginTimeout(5);
            Connection connection = DriverManager.getConnection(
                    databaseConfig.getUrl(),
                    databaseConfig.getUserName(),
                    databaseConfig.getUserPwd());

            //特殊处理PG判断schema是否存在
            if(connection != null && databaseConfig.getType().equals(DataBase.POSTGRESQL)) {
                SqlAdapter sqlAdapter = new SqlAdapter().setDataBase(databaseConfig.getType())
                        .setDbName(databaseConfig.getDbName())
                        .setSchema(databaseConfig.getSchema())
                        .setTableName(databaseConfig.getTableName());

                Query query = new Query(databaseConfig, sqlAdapter.getSchemaSQL(databaseConfig.getSchema())) {
                    @Override
                    public Object doProcess() throws Exception {
                        return getRs().get(0);
                    }
                };
                Map<String, Object> result = (Map<String, Object>) new DatabaseEngine().executeQuery(query);
                boolean r = (boolean) result.get("exists");
                return r;
            }
            return connection != null;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 获取主键 因为字段容易变，所以将这个方法提到这里来判断
     * @return
     */
    public static String getColumnKey(List<Map<String, Object>> header){
        String columnKeyCol = SQLConstants.ID;
        if (CollUtil.isEmpty(header)){
            return columnKeyCol;
        }
        for (Map<String, Object> map: header) {
            Object columnKey = map.get("columnKey");
            Object columnName = map.get("columnName");
            if (columnKey == null || columnName == null){
                continue;
            }
            String colName = columnName.toString();
            //避免id大小写问题
            if (SQLConstants.ID.equalsIgnoreCase(colName) ){
                columnKeyCol = colName;
            }
            if ("是".equals(columnKey.toString())){
                columnKeyCol = colName;
                break;
            }
        }
        return columnKeyCol;
    }
}
